package cn.ztydata.teachingsystem.heaven.dao;

import cn.ztydata.teachingsystem.common.Page;
import cn.ztydata.teachingsystem.heaven.entity.CourseGroup;
import cn.ztydata.teachingsystem.heaven.entity.Teacher;
import org.apache.commons.lang3.StringUtils;
import org.springframework.dao.EmptyResultDataAccessException;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.core.namedparam.MapSqlParameterSource;
import org.springframework.jdbc.core.namedparam.SqlParameterSource;
import org.springframework.stereotype.Repository;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;
import java.util.Set;

/**
 * 历史课程群组dao
 * Created by wyh on 2015/3/27.
 */
@Repository
public class HcourseGroupDao extends BaseDao {

    /**
     * 批量保存课程群组
     * @return int
     *
     * @author wyh
     * @since 2015-03-26
     */
    public int saveAll(){
        String sql = "INSERT INTO t_hcourse_group (SELECT id, sCode, sName, sDeptNum, sTeacherNum FROM t_course_group)";

        log.debug("return 归档课程群组数据的结果");
        return namedParameterJdbcTemplate.update(sql, new MapSqlParameterSource());
    }

    /**
     * 历史课程群组列表
     * @param depart 系部编码
     * @param startYear 起始学年
     * @param term 学期 0上1下2全部
     * @param page 页码
     * @param pageSize 页数
     * @return Page
     *
     * @author wyh
     * @since 2015-03-30
     */
    public Page<CourseGroup> listHistoryGroup(String depart,  String startYear, int term, int page, int pageSize, final List<String> teacherNumList, final List<String> departCodeList){
        StringBuilder sql = new StringBuilder("SELECT cg.*, ca.* FROM t_hcourse_group cg LEFT JOIN t_course_arrange ca ON cg.sCode=ca.sCode ");
        StringBuilder sqlTotal = new StringBuilder("SELECT count(*) FROM t_hcourse_group cg LEFT JOIN t_course_arrange ca ON cg.sCode=ca.sCode ");
        StringBuilder where = new StringBuilder(" WHERE 0=0 ");
        MapSqlParameterSource sps = new MapSqlParameterSource();

        //筛选系部编码
        if(!StringUtils.isEmpty(depart)){
            log.debug("系部不为空：!StringUtils.isEmpty(depart)");
            where.append(" AND cg.sDeptNum=:depart ");
            sps.addValue("depart", depart);
        }

        //筛选起始学年
        if(!StringUtils.isEmpty(startYear)){
            log.debug("学年不为空：!StringUtils.isEmpty(startYear)");
            where.append(" AND ca.sStartYear=:startYear ");
            sps.addValue("startYear", startYear);
        }

        //筛选学期
        if(term != 2){
            log.debug("筛选学期：term != 2");
            where.append("AND ca.tiTerm=:term ");
            sps.addValue("term", term);
        }

        //查询总数
        int total = namedParameterJdbcTemplate.queryForObject(sqlTotal.append(where).toString(), sps, Integer.class);
        if(total == 0){
            log.debug("查询总数为0：total == 0");
            return new Page<>();
        }

        //limit
        sql.append(where).append(" ORDER BY sStartYear DESC, sEndYear DESC, tiTerm DESC ").append(getLimitStr(page, pageSize));

        List<CourseGroup> groupList = namedParameterJdbcTemplate.query(sql.toString(), sps, new RowMapper<CourseGroup>() {
            @Override
            public CourseGroup mapRow(ResultSet resultSet, int i) throws SQLException {
                CourseGroup cg = new CourseGroup();
                cg.setId(resultSet.getLong("id"));
                cg.setCode(resultSet.getString("sCode"));
                cg.setName(resultSet.getString("sName"));
                cg.setTeacherNum(resultSet.getString("sTeacherNum"));
                cg.setDeptNum(resultSet.getString("sDeptNum"));
                cg.setYear(resultSet.getString("sStartYear"));
                //warn：记录为null时getInt方法返回0！
                cg.setTerm(resultSet.getInt("tiTerm"));

                teacherNumList.add(resultSet.getString("sTeacherNum"));
                departCodeList.add(resultSet.getString("sDeptNum"));
                return cg;
            }
        });

        log.debug("返回 历史课程群组列表");
        return new Page<>(total, pageSize, page, groupList);
    }

    /**
     * 获取与数据库中重复的课程代码
     * @param courseGroupCodeSet 课程群组课程代码集合
     * @return String
     *
     * @author wyh
     * @since 2015-04-22
     */
    public String getDuplicateCode(final Set<String> courseGroupCodeSet) {
        if (courseGroupCodeSet.isEmpty()) {
            log.debug("导入的课程群组课程代码集合为空");

            log.info("课程群组课程代码集合为0");
            return null;
        }

        try {
            String sql = "SELECT sCode FROM t_hcourse_group WHERE sCode IN (:numSet) LIMIT 1";
            SqlParameterSource sps = new MapSqlParameterSource().addValue("numSet", courseGroupCodeSet);

            log.debug("return 历史表中与导入数据重复的课程群组课程代码");
            return namedParameterJdbcTemplate.queryForObject(sql, sps, String.class);
        } catch (EmptyResultDataAccessException ex) {
            log.debug("历史数据库中无重复的课程群组课程代码");

            log.info("历史数据库中无重复的课程群组课程代码");
            return null;
        }
    }


    /**
     * 根据群组ID获取负责人头像、姓名
     *
     * @param groupId 群组ID
     * @return Long
     * @author lyj
     * @since 2015-3-31
     */
    public Teacher getLeaderId(final long groupId){
        StringBuilder sql = new StringBuilder("SELECT id,sName,sPicture FROM t_teacher WHERE sNumber = (SELECT sTeacherNum FROM t_hcourse_group WHERE id = :groupId)");

        try{
            log.debug("根据群组ID获取负责人头像、姓名");
            return namedParameterJdbcTemplate.queryForObject(sql.toString(), new MapSqlParameterSource().addValue("groupId", groupId), new RowMapper<Teacher>() {
                @Override

                public Teacher mapRow(ResultSet resultSet, int i) throws SQLException {
                    Teacher teacher = new Teacher();

                    teacher.setId(resultSet.getLong("id"));
                    teacher.setName(resultSet.getString("sName"));
                    teacher.setPicture(resultSet.getString("sPicture"));

                    return teacher;
                }
            });

        } catch (EmptyResultDataAccessException ex){
            log.debug("获取课程群组负责人的ID失败");
            log.warn("获取课程群组负责人的ID失败！groupId->{}", groupId);
            return null;
        }
    }


    /**
     * 课程群组详细
     * @param groupId 群组编号
     * @return CourseGroup
     *
     * @author fanruiping
     * @since 2015-03-26
     */
    public CourseGroup getById(Long groupId){
        try{
            String sql = "SELECT id, sCode, sName, sDeptNum From t_hcourse_group WHERE id=:id";

            return namedParameterJdbcTemplate.queryForObject(sql, new MapSqlParameterSource().addValue("id", groupId), new RowMapper<CourseGroup>() {
                @Override
                public CourseGroup mapRow(ResultSet rs, int i) throws SQLException {
                    CourseGroup group = new CourseGroup();

                    group.setId(rs.getLong("id"));
                    group.setCode(rs.getString("sCode"));
                    group.setName(rs.getString("sName"));
                    group.setDeptNum(rs.getString("sDeptNum"));

                    return group;
                }
            });
        }catch (EmptyResultDataAccessException ex){
            log.debug("未获取到指定课程群组信息:EmptyResultDataAccessException ex");

            return null;
        }
    }
}
